Skip to main content

DML with multiple join condition in dataset

This document contains information on a feature DML.DML commands are mainly used for manipulation with the records in our table, so with them, we can select/read data with some criteria or not, we can insert new data or edit existing ones and of course we can delete records if we don't need them anymore.

In this Scenario we are creating DML with multiple inner join condition city & state. Create an Analysis widget using DML dataset.

Docusaurus Slash Introduction

  1. Click on Hamburger Icon.
  2. Click on Master Data Tab.

Docusaurus Slash Introduction

  1. In Master Data Tab click on Dataset.
  2. Go to Dataset section, Click on Create DML from bottom menu bar. As shown in figure below:

Docusaurus Slash Introduction

  1. The Create Data Module dialog box looks as shown in figure below:

Docusaurus Slash Introduction

  1. Enter Following detail in DML Module dialog box:

    Name: DML with multiple inner join condition Source: ClassicModels

  2. Expend tables from left side table view, As shown in figure below:

Docusaurus Slash Introduction

  1. Drag and Drop the OFFICES table from the Table list to Right side diagram box, then select all the columns of OFFICES table, click on submit button.

  2. Select all the columns and click on submit button, as shown in figure below:

Docusaurus Slash Introduction

  1. After submitting, the diagram box will look like the figure below:

Docusaurus Slash Introduction

  1. Drag and drop the CUSTOMERS from the left table menu to right diagram box.

  2. Select all the column from CUSTOMERS table, and click submit button.

  3. After adding OFFICES and CUSTOMERS from the table list, Drag the COUNTRY column from Offices to Customers, as shown in figure below:

Docusaurus Slash Introduction

  1. Click on the thread between two tables, to add DML configuration.

  2. The DML configuration control box will open, Select the Inner Join radio button and Click on Submit button, as shown in figure below:

Docusaurus Slash Introduction

  1. Click on the Add Condition Button.

  2. Enter following details in it.

    Select Field: customers.State Enter value: ${customers.state}

  3. Click on + icon to add another new condition and enter following details in it.

    Select field: Customers.city Enter value: ${customers.city}

  4. Fill above details, as shown in figure below:

Docusaurus Slash Introduction

  1. Click on Get Condition Button.
  2. Click on Submit Button.
  3. Note: Left Node should have OFFICES and Right Node should have CUSTOMERS.
  4. The Connection between two tables will looks as shown in figure below:

Docusaurus Slash Introduction

  1. Click on Submit Button.
  2. Create a new Dashboard from the Visualization section.
  3. After loading the Dashboard, Find Analysis Icon from the left vertical menu bar, click on the icon to load analysis widget in dashboard, as shown in figure below:

Docusaurus Slash Introduction

  1. Enter following in Analysis Widgets.

    Dataset: DML with multiple inner join condition Select Edit Analysis

  2. Fill above details, as shown in figure below:

Docusaurus Slash Introduction

  1. Click on Save and Exit.
  2. Click on Show Field list Docusaurus Slash Introduction Icon, as shown in figure below:

Docusaurus Slash Introduction

  1. Expand the Customer Table and drag and drop country column in Rows, Creditlimits in Values.
  2. In Values Type select sum and click on save.
  3. Expand the Office Table and drag and drop state in columns and city in Rows, as shown in figure below:

Docusaurus Slash Introduction

  1. Click on Preview and Save and Exit.
  2. The output of the widget will look like available figure below:

Docusaurus Slash Introduction

More features of OPNBI as follows:

DML Inner Join

DML Left Join

DML Right Join

DML Language Alias

DML using Parameter